package com.bobo.dao.report;

import com.bobo.dao.BaseDao;
import com.bobo.dto.ReportDto;
import com.bobo.model.Message;
import com.bobo.model.Report;
import com.bobo.model.Topic;
import com.bobo.model.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by bobo on 16-11-3.
 */
public class ReportDaoImpl extends BaseDao<Report> implements IReportDao {
    @Override
    public Integer create(Report report) {
        String sql = "INSERT INTO t_report(user_id, message_id, content, create_time)VALUES (?,?,?,now())";
        return create(sql,report.getUser().getId(),report.getMessage().getId(),report.getContent());
    }

    @Override
    public Report getById(Integer id) {
        String sql = "SELECT tr.id trid,tr.content trc, " +
                "  tu.id userid,tu.name," +
                "  tm.id messageid,tm.content tmc " +
                "  FROM t_report tr,t_user tu,t_message tm " +
                " WHERE tr.user_id=tu.id AND tr.message_id=tm.id " +
                " AND tr.id=?";
        Report report = new Report();
        ResultSet rs = getObject(sql, id);
        try {
            while (rs.next()){
                report.setId(rs.getInt("trid"));
                report.setContent(rs.getString("trc"));

                User user = new User();
                user.setId(rs.getInt("userid"));
                user.setName(rs.getString("name"));
                report.setUser(user);

                Message message = new Message();
                message.setId(rs.getInt("messageid"));
                message.setContent(rs.getString("tmc"));
                report.setMessage(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return report;
    }

    @Override
    public List<Report> getReports(Integer messageId,Integer current, Integer pageSize) {

        String sql = "select tu.name,tr.id, tr.content, tr.create_time  from t_report tr \n" +
                "  LEFT JOIN t_user tu on tr.user_id = tu.id\n" +
                "where message_id = ? ";

        List<Report> reports = new ArrayList<>();
        ResultSet rs = pager(sql, current, pageSize, messageId);
        try {
            while (rs.next()){
                Report report = new Report();
                report.setId(rs.getInt("id"));
                report.setContent(rs.getString("content"));
                report.setCreateTime(rs.getDate("create_time"));

                User user = new User();
                user.setName(rs.getString("name"));

                report.setUser(user);
                reports.add(report);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reports;
    }

    @Override
    public List<ReportDto> getReportDtos(Integer current, Integer pageSize) {
        // 列表的sql
        String sql = "SELECT tt.id as topic_id, tt.title, tm.id as message_id, tm.content,count(tr.id) as reports FROM  t_message tm\n" +
                "  LEFT JOIN  t_report tr on tm.id = tr.message_id\n" +
                "  LEFT JOIN  t_topic tt on tm.topic_id = tt.id\n" +
                "  where tm.status = 1\n" +
                "GROUP BY  tm.id\n" +
                "ORDER BY reports  desc";
        List<ReportDto> reportDtos = new ArrayList<>();
        ResultSet rs = pager(sql, current, pageSize);
        try {
            while (rs.next()){
                ReportDto reportDto = new ReportDto();
                Topic topic = new Topic();
                topic.setId(rs.getInt("topic_id"));
                topic.setTitle(rs.getString("title"));

                reportDto.setTopic(topic);
                reportDto.setMessageId(rs.getInt("message_id"));
                String ct = rs.getString("content");
                if(ct.length()>10){
                    ct=ct.substring(0,10);
                }
                reportDto.setContent(ct);
                reportDto.setReports(rs.getInt("reports"));

                reportDtos.add(reportDto);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reportDtos;
    }

    @Override
    public Integer getCount(Integer messageId) {
        Integer count;
        String sql = "where message_id="+messageId;
        count = getCount(sql);
        return count;
    }

    @Override
    public List<Message> getReportByUser(User user, Topic topic) {

        String sql = "SELECT  tm.id from t_report tr\n" +
                "  LEFT JOIN t_message tm ON  tr.message_id = tm.id\n" +
                "  where tm.topic_id = ?\n" +
                "  and tr.user_id = ?\n";

        List<Message> list = new ArrayList<>();
        ResultSet rs = getObject(sql, topic.getId(), user.getId());

        try {
            while (rs.next()) {
                Message msg = new Message();
                msg.setId(rs.getInt("id"));
                list.add(msg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }
}
